package swu.myt;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

public class DBUtils {
	private static String DB_DRIVER = "com.mysql.cj.jdbc.Driver";
	private static String DB_URL    = "jdbc:mysql://localhost:3306/user_info";
	private static String DB_USER   = "root";
	private static String DB_PASS   = "123456";
	

	
	//关闭资源
	public static void closeAll(ResultSet rs,Statement stmt,Connection conn) {
		if(rs!=null) {
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		if(stmt!=null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		if(conn!=null) {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	public static User getUser(String sql) throws SQLException {
		try {
			Class.forName(DB_DRIVER);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		
		User user = new User();
		
		try (Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS)) {
			try (Statement statement = connection.createStatement()) {
				ResultSet rs = statement.executeQuery(sql);				
				while (rs.next()) {
					user.setId(rs.getInt("user_id"));
					user.setName(rs.getString("user_name"));
					user.setPass(rs.getString("user_pass"));
				}
			}
		}
		System.out.println(sql);
		return user;
		
	}
		
	public static void addUser(String username,String pass) throws SQLException {
		try {
			Class.forName(DB_DRIVER);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		
		try (Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS)) {
			try (Statement statement = connection.createStatement()) {		
				
				String Sql_addUser = "INSERT INTO user_login (user_name,user_pass,submission_date)"
						+ "VALUES ("+username+","+pass+",now());";
				statement.executeUpdate(Sql_addUser);//执行语句
				System.out.println("插入到数据库成功");
				
				String Sql_addTabel = "CREATE TABLE "+username+"Says(say VARCHAR(500),submission_date DATE, img longblob);";
				System.out.println(Sql_addTabel);
				statement.executeUpdate(Sql_addTabel);//执行语句
				System.out.println("新增表成功，表中存放日记");					
				
				DBUtils.addsaying(username, "created my first saying!",null);
				
				
			}
		}
		
		
	}
	
	public static void addsaying(String username,String saying,String pic) throws SQLException {
		try {
			Class.forName(DB_DRIVER);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		
		try (Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS)) {
			try (Statement statement = connection.createStatement()) {		
				String Sql = "INSERT INTO "+username+"Says (say,submission_date,img) "
						+ " VALUES ('"+saying+"',now(),'"+pic+"');";
				
				System.out.println(Sql);
				statement.executeUpdate(Sql);//执行语句
				
				System.out.println("添加日记成功");
				
			
			}
		}
	}
	
	public static List<Say> getsayings(String username) throws SQLException {
		try {
			Class.forName(DB_DRIVER);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		
		List<Say> says = new ArrayList<Say>();
		
		String sql = "SELECT * FROM "+username+"says order by submission_date desc;";
		
		try (Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS)) {
			try (Statement statement = connection.createStatement()) {
				ResultSet rs = statement.executeQuery(sql);				
				while (rs.next()) {
					Say say = new Say();
					say.setSay(rs.getNString("say"));
					say.setDate(rs.getDate("submission_date"));
					say.setPic(rs.getString("img"));
					says.add(say);
				}
			}
		}				
		return says;
	}
	
	public static List<Say> limGetsayings(String sql) throws SQLException {
		try {
			Class.forName(DB_DRIVER);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		
		List<Say> says = new ArrayList<Say>();
		
		try (Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS)) {
			try (Statement statement = connection.createStatement()) {
				ResultSet rs = statement.executeQuery(sql);				
				while (rs.next()) {
					Say say = new Say();
					say.setSay(rs.getNString("say"));
					say.setDate(rs.getDate("submission_date"));
					say.setPic(rs.getString("img"));
					says.add(say);
				}
			}
		}				
		return says;
	}
	
	public static Boolean deletesayings(String sql)  {
		try {
			Class.forName(DB_DRIVER);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		
		
		try (Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS)) {
			Statement statement = connection.createStatement();
				ResultSet rs = statement.executeQuery(sql);		
				return true;
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return false;
			}		
	}
	
	
    
}
